<?php

class UserServiceFindCommand implements Command {
	private $controls;
	public function __construct(UserServiceSearchControls $controls){
		$this->controls = $controls;
	}
	public function doExecute($connection){
			$result = array();
			
			$query = "select st." .
				BoaConstans::SERVICE_COLUMN_ID . ", st." .
			BoaConstans::SERVICE_COLUMN_NAME   . ", st." .
			BoaConstans::SERVICE_COLUMN_DESCR  . ", st." .
			BoaConstans::SERVICE_COLUMN_ACTIVE . ", st." .
			BoaConstans::SERVICE_COLUMN_PERIOD . ", ut." .
			BoaConstans::USER_COLUMN_NAME . ", ut." .
			BoaConstans::USER_COLUMN_SURNAME . ", ust." .
			BoaConstans::USER_SERVICE_COLUMN_ID  . ", ust." .
			BoaConstans::USER_SERVICE_COLUMN_PRICE  . ", ust." .
			BoaConstans::USER_SERVICE_COLUMN_DATE_TO . ", ust." .
			BoaConstans::USER_SERVICE_COLUMN_DATE_FROM .
			" from " . BoaConstans::SERVICE_TABLE . " st join " . BoaConstans::USER_SERVICE_TABLE . " ust on st." .
			BoaConstans::SERVICE_COLUMN_ID . "= ust." . BoaConstans::USER_SERVICE_COLUMN_SID . 
			" join " . BoaConstans::USER_TABLE . " ut on ust." . BoaConstans::USER_SERVICE_COLUMN_UID . "=ut." .
			 	BoaConstans::USER_COLUMN_ID;
			
			$where = null;
			$types = "";
			$params = array();
			$aid = VariableUtils::isBoolean($this->controls->getActiveInDays()) ? $this->controls->getActiveInDays() : null; 
	 		if($this->controls->getFromDate() != null && $aid == null){
			 	$types .="s";
			 	$params[] = $this->controls->getFromDate();
			 	$this->addToWhere($where, "ust." . BoaConstans::USER_SERVICE_COLUMN_DATE_FROM . ">=?");
			 }
			 
			 if($this->controls->getToDate() != null && $aid == null){
			 	$types .="s";
			 	$params[] = $this->controls->getToDate();
			 	$this->addToWhere($where, "ust." .BoaConstans::USER_SERVICE_COLUMN_DATE_TO ." <=?");
			 }
			 
			 
			 if($aid != null && $this->controls->getToDate() != null && $this->controls->getFromDate()){
			 	
			 	$types .="ssssssssss";
			 	$params[] = $this->controls->getFromDate();
			 	$params[] = $this->controls->getToDate();
			 	
			 	$params[] = $this->controls->getFromDate();
			 	$params[] = $this->controls->getToDate();
			 	
			 	$params[] = $this->controls->getFromDate();
			 	$params[] = $this->controls->getToDate();
			 	$params[] = $this->controls->getFromDate();
			 	
			 	$params[] = $this->controls->getFromDate();
			 	$params[] = $this->controls->getToDate();
			 	$params[] = $this->controls->getToDate();
			 	
			 	$activeQuery = "((ust." . BoaConstans::USER_SERVICE_COLUMN_DATE_FROM . "<=? and " .
			 				   "ust." . BoaConstans::USER_SERVICE_COLUMN_DATE_TO . ">=?) or (" .
			 					"ust." . BoaConstans::USER_SERVICE_COLUMN_DATE_FROM . ">=? and " .
			 	  			   "ust." . BoaConstans::USER_SERVICE_COLUMN_DATE_TO . "<=?) or
			 	  			   
			 	  			   (ust." . BoaConstans::USER_SERVICE_COLUMN_DATE_FROM . "<=? and
			 	  			   	ust." . BoaConstans::USER_SERVICE_COLUMN_DATE_TO . "<=? and 
			 	  			   	ust." . BoaConstans::USER_SERVICE_COLUMN_DATE_TO . ">=?) or
			 	  			   (ust." . BoaConstans::USER_SERVICE_COLUMN_DATE_FROM . ">=? and
			 	  			   	ust." . BoaConstans::USER_SERVICE_COLUMN_DATE_TO. ">=? and
			 	  			   	ust." . BoaConstans::USER_SERVICE_COLUMN_DATE_FROM . "<=?)
			 	  			   )";
			 	$this->addToWhere($where, $activeQuery);
			 }
			 
			 
			 
			 if(VariableUtils::isNumber($this->controls->getUserId())){
			 	$types.="i";
			 	$params[] = $this->controls->getUserId();
			 	$this->addToWhere($where, "ust." . BoaConstans::USER_SERVICE_COLUMN_UID ."=?");
			 }
			 
			 if(VariableUtils::isNumber($this->controls->getServiceId())){
			 	$types.="i";
			 	$params[] = $this->controls->getServiceId();
			 	$this->addToWhere($where, "ust." . BoaConstans::USER_SERVICE_COLUMN_SID ."=?");
			 }
			 
			 if(VariableUtils::isBoolean($this->controls->isTicket())){
			 	$ticketQuery = null;
			 	$types.="i";
			 	$params[] = true;
			 	if(VariableUtils::getBoolean($this->controls->isTicket())){
			 		$ticketQuery = "ust." . BoaConstans::USER_SERVICE_COLUMN_ID. " in(select tt." . BoaConstans::TICKET_COLUMN_USID . " from " .
			 		BoaConstans::TICKET_TABLE . " tt where tt." . BoaConstans::TICKET_COLUMN_ACTIVE . "=?)";
			 	}else{
			 		$ticketQuery = "ust." . BoaConstans::USER_SERVICE_COLUMN_ID. " not in(select tt." . BoaConstans::TICKET_COLUMN_USID . " from " .
			 		BoaConstans::TICKET_TABLE . " tt where tt." . BoaConstans::TICKET_COLUMN_ACTIVE . "=?)";
			 	}
			 	$this->addToWhere($where, $ticketQuery);
			 }
			 
			 
			 if($this->controls->getServiceName() != null){
			 	$types.="s";
			 	$params[]  = VariableUtils::getRealEscapeString($this->controls->getServiceName());
			 	$this->addToWhere($where, "upper(st.".BoaConstans::SERVICE_COLUMN_NAME . ") like upper(CONCAT('%', ?, '%'))"); 
			 }
			 
			 
			if($this->controls->getUserName() != null){
			 	$types.="s";
			 	$params[]  = VariableUtils::getRealEscapeString($this->controls->getUserName());
			 	$this->addToWhere($where, "upper(ut.".BoaConstans::USER_COLUMN_NAME . ") like upper(CONCAT('%', ?, '%'))");
			 }
			 
			if($where != null){
				$query.=" where " . $where;				
			}
		
			
			$stmt = $connection->prepare($query);
			if($types != null && $types != ""){
				$bind_param = array();
				$bind_param[] = $types;
				for($i=0;$i<sizeof($params);$i++){
					$bind_param[] = &$params[$i];
				}
				call_user_func_array(array($stmt, 'bind_param'), $bind_param);
			}
			$stmt->execute();
			
			$id = null;	
			$usid = null;
			$name = null;
			$descr = null;
			$active = null;
			$period = null;
			$userName = null;
			$userSurName = null;
			$price = null;	
			$date_to = null;
			$date_from = null;		
			$stmt->bind_result($id, $name, $descr, $active, $period, $userName, $userSurName, $usid, $price,$date_to, $date_from);
			while($stmt->fetch()){
				$us = new UserService(ServiceConverter::convert($id, $name, $descr, $active, $period, $price));
				$us->setId($usid);
				$us->setDateFrom($date_from);
				$us->setDateTo($date_to);
				$us->setFullUserName($userName . " "  . $userSurName);
				$result[] = $us;
			}
			$stmt->close();
			
			foreach($result as $service){
				$spsc = new ServicePartSearchControls();
				$spsc->setSid($service->getId());
				$spfc = new ServicePartFindCommand($spsc);
				$parts = $spfc->doExecute($connection);
				foreach($parts as $part){
					$service->addPart($part);
				}
			}
			
			return $result;
	}
	
	private function addToWhere(&$where, $condition){
			if($where == null){
					$where = $condition;
			}else{
				$where.= " and " . $condition;
			}
	}
}


class UserServicePayCommand implements Command{
	private $service;
	private $part;
	public function __construct(UserService $service, $part){
		$this->service = $service;
		$this->part = $part;
	}
	
	public function doExecute($connection){
		if($this->service == null || $this->part == null || !VariableUtils::isNumber($this->service->getId()) || !VariableUtils::isNumber($this->part)){
			return false;		
		}
		
		$query = "update " . BoaConstans::PAYMENT_TABLE . " pt set pt." .
		BoaConstans::PAYMENT_COLUMN_IS_PAID . "=? where pt." .
		BoaConstans::PAYMENT_COLUMN_PART . "=?  and pt." . 
		BoaConstans::PAYMENT_COLUMN_USID . "=?";
		$stmt = $connection->prepare($query);
		$usid = $this->service->getId();
		$is_paid = "1";
		$stmt->bind_param("iii", $is_paid, $this->part, $usid);
		$stmt->execute();
		$stmt->close();
		return true;	
	}
}

class UserServiceBuyCommand  implements Command{
	private $service;
	private $user;
	
	public function __construct(Service $service, User $user ){
		$this->service = $service;
		$this->user = $user;
	}
	
	public function doExecute($connection){
		if($this->service == null || $this->user == null || !VariableUtils::isNumber($this->service->getId()) ||
		!VariableUtils::isNumber($this->user->getId()) || !VariableUtils::isNumber($this->service->getPeriod()) ){ 
			return false;
		}
	
		$sid = $this->service->getId();
		$uid = $this->user->getId();
		$period = $this->service->getPeriod();
		$selectPromotionQuery = "select pt." . BoaConstans::PROMOTION_COLUMN_VALUE . " from " . 
		BoaConstans::PROMOTION_TABLE . " pt join " . BoaConstans::PROMOTION_SERVICE_TABLE . " pst on  pt." . BoaConstans::PROMOTION_COLUMN_ID . " = pst." .
		BoaConstans::PROMOTION_SERVICE_COLUMN_PID . " where pst." . BoaConstans::PROMOTION_SERVICE_COLUMN_SID . "=?";
		$stmt = $connection->prepare($selectPromotionQuery);
		$stmt->bind_param("i", $sid);
		$stmt->execute();
		
		$promotionValue = null;
		$stmt->bind_result($promotionValue);
		$stmt->fetch();
		$stmt->close();
		
		$query = "insert into " . BoaConstans::USER_SERVICE_TABLE . "(" .
		BoaConstans::USER_SERVICE_COLUMN_UID . ", " .
		BoaConstans::USER_SERVICE_COLUMN_SID . ", " .
		BoaConstans::USER_SERVICE_COLUMN_DATE_FROM . ", " .
		BoaConstans::USER_SERVICE_COLUMN_DATE_TO . ", " .
		BoaConstans::USER_SERVICE_COLUMN_PRICE . ", " .
		BoaConstans::USER_SERVICE_COLUMN_WARN .	") values(?,?,?,?,?,?)";
				
		$price = $this->service->getPrice() * (VariableUtils::isNumber($promotionValue) && $promotionValue != 0 ? (100-$promotionValue)/100 : 1) ;
		$dateFrom = @date("Y-m-d");
		$dateTo = @date("Y-m-d", @strtotime("+$period month"));
		$warn = 0;
		$stmt = $connection->prepare($query);
		$stmt->bind_param("iissii",$uid , $sid, $dateFrom, $dateTo, $price, $warn);
		$stmt->execute();
		$usid  = $stmt->insert_id;
		$stmt->close();
		
		$paymentQuery = "insert into " . BoaConstans::PAYMENT_TABLE  . "(" . 
			BoaConstans::PAYMENT_COLUMN_USID . ", " .
			BoaConstans::PAYMENT_COLUMN_PART . ", " .
			BoaConstans::PAYMENT_COLUMN_IS_PAID . ", " .
			BoaConstans::PAYMENT_COLUMN_PAID_TILL_DATE . ") values(?,?,?,?)";
		$stmt = $connection->prepare($paymentQuery);
		for($i = 0; $i < $period ; $i++){
			$part = $i+1;
			$is_paid = 0;
			$till_date = @date("Y-m-d", @strtotime("+$part month"));
			$stmt->bind_param("iiss",$usid, $part, $is_paid, $till_date);
			$stmt->execute();
		}
		$stmt->close();
		return $usid;
	}
}


class UserServiceGetCommand implements Command{
	
	private $id;
	
	public function __construct($id){
		$this->id = $id;
	}
	
	public function doExecute($connection){
			
			$query = "select ust." .
				BoaConstans::SERVICE_COLUMN_ID . ", st." .
			BoaConstans::SERVICE_COLUMN_NAME   . ", st." .
			BoaConstans::SERVICE_COLUMN_DESCR  . ", st." .
			BoaConstans::SERVICE_COLUMN_ACTIVE . ", st." .
			BoaConstans::SERVICE_COLUMN_PERIOD . ", ut." .
			BoaConstans::USER_COLUMN_NAME . ", ut." .
			BoaConstans::USER_COLUMN_SURNAME . ", ust." .
			BoaConstans::USER_SERVICE_COLUMN_PRICE  . ", ust." .
			BoaConstans::USER_SERVICE_COLUMN_DATE_TO . ", ust." .
			BoaConstans::USER_SERVICE_COLUMN_DATE_FROM .
			" from " . BoaConstans::SERVICE_TABLE . " st join " . BoaConstans::USER_SERVICE_TABLE . " ust on st." .
			BoaConstans::SERVICE_COLUMN_ID . "= ust." . BoaConstans::USER_SERVICE_COLUMN_SID . 
			" join " . BoaConstans::USER_TABLE . " ut on ust." . BoaConstans::USER_SERVICE_COLUMN_UID . "= ut." .
			 	BoaConstans::USER_COLUMN_ID . " where ust." . BoaConstans::USER_SERVICE_COLUMN_ID . "=?";
			 	
			 	
			$stmt = $connection->prepare($query);
			$stmt->bind_param("i", $this->id);
			$stmt->execute();
			$id = null;	
			$name = null;
			$descr = null;
			$active = null;
			$period = null;
			$userName = null;
			$userSurName = null;
			$price = null;	
			$date_to = null;
			$date_from = null;	
			$stmt->bind_result($id, $name, $descr, $active, $period, $userName, $userSurName, $price,$date_to, $date_from);
			
			$us = null;
			if($stmt->fetch()){
				$us = new UserService(ServiceConverter::convert($id, $name, $descr, $active, $period, $price));
				$us->setId($this->id);
				$us->setDateFrom($date_from);
				$us->setDateTo($date_to);
				$us->setFullUserName($userName . " "  . $userSurName);
			
				$stmt->close();
			
				$spsc = new ServicePartSearchControls();
				$spsc->setSid($id);
				$spfc = new ServicePartFindCommand($spsc);
				$parts = $spfc->doExecute($connection);
				foreach($parts as $part){
					$us->addPart($part);
				}
			}else{
				$stmt->close();
			}
		
			return $us;
	}
}
?>